| Oracle 数据泵(expdp和impdp)排除某个表 – 小麦苗DBA宝典 | 您所在的位置：网站首页 › expdp reuse_dumpfiles › Oracle 数据泵(expdp和impdp)排除某个表 – 小麦苗DBA宝典 |
|
本页目录 隐藏 实验 How to Exclude Multiple Schemas and Tables in Export or Import (Doc ID 2087052.1) APPLIES TO: GOAL SOLUTION REFERENCES How to Exclude Duplicate Tables From Some Schemas During a Full DataPump Export (Doc ID 1474932.1) APPLIES TO: SYMPTOMS CHANGES CAUSE SOLUTION REFERENCES Export/Import DataPump Parameter TABLES - How to Export and Import Tables Residing in Different Schemas (Doc ID 277905.1) APPLIES TO: GOAL SOLUTION Solution 1: Use combination of SCHEMAS and INCLUDE parameters. Solution 2: Use multiple Data Pump jobs. REFERENCES 总结 解决办法： 其它 相关文章 实验 环境：LHR和LHR1用户下都有T3表，如下： 123456789101112131415161718192021222324252627282930[oracle@lhrora11204 ~]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Export: Release 11.2.0.4.0 - Production on Sat Dec 3 03:08:27 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 40 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLE. . exported "LHR1"."T2" 950.1 KB 86961 rows. . exported "LHR"."T3" 950.1 KB 86962 rows. . exported "LHR"."T1" 950.1 KB 86960 rowsProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA. . exported "LHR1"."T3" 950.2 KB 86964 rowsProcessing object type SCHEMA_EXPORT/TABLE/TABLEMaster table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /home/oracle/lhr_01.dmp /home/oracle/lhr_02.dmp /home/oracle/lhr_03.dmp /home/oracle/lhr_04.dmpJob "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 3 03:08:34 2022 elapsed 0 00:00:07如下，排除T3表，会排除LHR和LHR1这2个用户下的T3表： 123456789101112131415161718192021222324252627[oracle@lhrora11204 ~]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS,TABLE:"in('T3')" LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Export: Release 11.2.0.4.0 - Production on Sat Dec 3 03:10:41 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS,TABLE:in('T3') LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 20 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLE. . exported "LHR"."T1" 950.1 KB 86960 rows. . exported "LHR1"."T2" 950.1 KB 86961 rowsProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEMaster table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /home/oracle/lhr_01.dmp /home/oracle/lhr_02.dmp /home/oracle/lhr_03.dmpJob "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 3 03:10:48 2022 elapsed 0 00:00:07如下写法并不能排除T3表： 123456789101112131415161718192021222324252627282930[oracle@lhrora11204 ~]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS,TABLE:"in('LHR.T3')" LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Export: Release 11.2.0.4.0 - Production on Sat Dec 3 03:09:55 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS,TABLE:in('LHR.T3') LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 40 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLE. . exported "LHR1"."T2" 950.1 KB 86961 rows. . exported "LHR"."T3" 950.1 KB 86962 rows. . exported "LHR"."T1" 950.1 KB 86960 rowsProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA. . exported "LHR1"."T3" 950.2 KB 86964 rowsProcessing object type SCHEMA_EXPORT/TABLE/TABLEMaster table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /home/oracle/lhr_01.dmp /home/oracle/lhr_02.dmp /home/oracle/lhr_03.dmp /home/oracle/lhr_04.dmpJob "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 3 03:10:03 2022 elapsed 0 00:00:07解决：只导出元数据，不导出具体数据： 1234567891011121314151617181920212223242526272829303132333435 [oracle@lhrora11204 ~]$ more parfile.par QUERY=LHR.T3:"where 1 1"[oracle@lhrora11204 ~]$ [oracle@lhrora11204 ~]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y parfile=parfile.par Export: Release 11.2.0.4.0 - Production on Sat Dec 3 03:35:28 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y parfile=parfile.par Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 40 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLE. . exported "LHR"."T1" 950.1 KB 86960 rows. . exported "LHR1"."T2" 950.1 KB 86961 rows. . exported "LHR1"."T3" 950.2 KB 86964 rowsProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA. . exported "LHR"."T3" 5.585 KB 0 rowsProcessing object type SCHEMA_EXPORT/TABLE/TABLEMaster table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /home/oracle/lhr_01.dmp /home/oracle/lhr_02.dmp /home/oracle/lhr_03.dmp /home/oracle/lhr_04.dmpJob "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 3 03:35:35 2022 elapsed 0 00:00:07使用查询，也会排除所有用户下的T3表： 1234567891011121314151617181920212223242526272829303132vi parfile.parEXCLUDE=STATISTICS,TABLE:"IN (select TABLE_NAME from DBA_TABLES where owner='LHR' AND table_name='T3')" expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp parfile=parfile.par LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y [oracle@lhrora11204 ~]$ expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp parfile=parfile.par LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Export: Release 11.2.0.4.0 - Production on Sat Dec 3 03:43:09 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=D1 dumpfile=lhr_%U.dmp parfile=parfile.par LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 20 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLE. . exported "LHR"."T1" 950.1 KB 86960 rows. . exported "LHR1"."T2" 950.1 KB 86961 rowsProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEMaster table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /home/oracle/lhr_01.dmp /home/oracle/lhr_02.dmp /home/oracle/lhr_03.dmpJob "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Dec 3 03:43:19 2022 elapsed 0 00:00:09How to Exclude Multiple Schemas and Tables in Export or Import (Doc ID 2087052.1)APPLIES TO:Oracle Database - Enterprise Edition - Version 11.2.0.1 and later Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Information in this document applies to any platform. NOTE: In the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner. GOALInclude or Exclude multiple tables from multiple schemas in full Export or Import. 本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！ SOLUTIONFollowing example will show to exclude or include multiple tables or schemas. It can be used either on the command line or in a parameter file. Example to use include on the command line to include all tables for owner AJ or BJ:12 expdp system/ full=Y directory= dumpfile=.dmp logfile=.log include=table:\"in \(select table_name from dba_tables where owner like \'AJ%\' or owner like \'BJ%\' \)\"Example to use exclude in a par file to exclude table A1_TAB owned by user AJ and B1_TAB owned by user BJ.Syntax: 12 EXCLUDE=TABLE:"IN (select table_name from dba_tables where (owner,table_name) in (('',''), ('','')) )"Example: 12 EXCLUDE=TABLE:"IN (select TABLE_NAME from DBA_TABLES where (owner,table_name) in (('AJ','A1_TAB'), ('BJ','B1_TAB')) )"There is another workaround mentioned in following document by using the QUERY parameter to make sure no rows for these tables are exported (only metadata is exported). Please refer to:Note 1474932.1 - How to Exclude Duplicate Tables From Some Schemas During a Full DataPump Export REFERENCESBUG:6860716 - EXPORT&FILTER TABLES FROM MULTIPLE SCHEMAS IN DATA PUMPNOTE:341733.1 - Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects How to Exclude Duplicate Tables From Some Schemas During a Full DataPump Export (Doc ID 1474932.1)APPLIES TO:Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Information in this document applies to any platform. SYMPTOMSYou need to do a full database export but the same table is present in several schemas and you want to exclude this table for one schemas only. CHANGESNONE CAUSEDuplicate table names. SOLUTIONUnfortunately, you cannot exclude the tables. From Note 277905.1: \1. Note that all specified tables in the selected schemas are exported, together with their dependent objects. It is not possible to include or exclude tables owned by a specific schema with a full database export DataPump job, or schema level export DataPump job with multiple schema's. \2. As a workaround, you can use the query parameter to make sure no rows for these tables are exported (only metadata is exported): 123 QUERY=.:"where 1 1"QUERY=.:"where 1 1"REFERENCESNOTE:277905.1 - Export/Import DataPump Parameter TABLES - How to Export and Import Tables Residing in Different Schemas Export/Import DataPump Parameter TABLES - How to Export and Import Tables Residing in Different Schemas (Doc ID 277905.1)APPLIES TO:Oracle Database - Personal Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1] Oracle Database - Standard Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1] Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1] Information in this document applies to any platform. NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner. GOALWith the classic export client you were able to export with one single export session, tables that were owned by different users. For example to export the following tables: - table EMP owned by user SCOTT - table EMPLOYEES owned by user HR - table CATEGORIES_TAB owned by user OE % exp system/ FILE=.dmp LOG=.log \ TABLES=scott.emp,hr.employees,oe.categories_tab Export: Release 11.1.0.6.0 - Production on Wed Nov 28 10:04:10 2007 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... Current user changed to SCOTT . . exporting table EMP 14 rows exported Current user changed to HR . . exporting table EMPLOYEES 107 rows exported Current user changed to OE . . exporting table CATEGORIES_TAB 22 rows exported . . exporting table PRODUCT_REF_LIST_NESTEDTAB 288 rows exported . . exporting table SUBCATEGORY_REF_LIST_NESTEDTAB 21 rows exported Export terminated successfully without warnings. However, with Export Data Pump you get the following error: % expdp system/ DIRECTORY= \ DUMPFILE=.dmp LOGFILE=.log \ TABLES=scott.emp,hr.employees,oe.categories_tab Export: Release 11.1.0.6.0 - Production on Wednesday, 28 November, 2007 10:07:13 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options UDE-00012: table mode exports only allow objects from one schema This is caused by a restriction for the TABLES parameter of Data Pump:"The table name that you specify can be preceded by a qualifying schema name. All table names specified must reside in the same schema." An enhancement request has been filed to make the syntax similar to that of the original export client:Bug 6860716 - EXPORT&FILTER TABLES FROM MULTIPLE SCHEMAS IN DATA PUMP This is implemented in version 11.2. How to achieve the same kind of export with Oracle Data Pump ? SOLUTIONSolution 1: Use combination of SCHEMAS and INCLUDE parameters.When using Data Pump you can use the combination of the SCHEMAS and INCLUDE parameters. Example: 1234567891011121314151617181920212223242526272829303132333435363738File: expdp_tabs.par--------------------DIRECTORY = DUMPFILE = expdp_tabs.dmp LOGFILE = expdp_tabs.log SCHEMAS = scott,hr,oe INCLUDE = TABLE:"IN ('EMP', 'EMPLOYEES', 'CATEGORIES_TAB')" % expdp system/ PARFILE=.par Export: Release 11.1.0.6.0 - Production on Wednesday, 28 November, 2007 10:25:56 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** parfile=.parEstimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 320 KB Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "HR"."EMPLOYEES" 16.80 KB 107 rows . . exported "OE"."CATEGORIES_TAB" 14.15 KB 22 rows . . exported "OE"."PRODUCT_REF_LIST_NESTEDTAB" 12.51 KB 288 rows . . exported "OE"."SUBCATEGORY_REF_LIST_NESTEDTAB" 6.593 KB 21 rows . . exported "SCOTT"."EMP" 8.570 KB 14 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: \.DMP Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:26:57Remarks: \1. Note that all specified tables in the selected schemas are exported, together with their dependent objects. It is not possible to include or exclude tables owned by a specific schema with a full database export Data Pump job, or schema level export Data Pump job with multiple schema's. \2. Note that if you want to specify these parameters on the command line, you have to use the escape character for special characters: Example for Windows (place all parameters on a single line): 123% expdp system/ DIRECTORY= DUMPFILE=.dmpLOGFILE=.log SCHEMAS=scott,hr,oe INCLUDE=TABLE:\"IN ('EMP', 'EMPLOYEES', 'CATEGORIES_TAB')\"Example for Unix/Linux (escape all special characters): 123expdp system/ DIRECTORY= \DUMPFILE=.dmp LOGFILE=.log SCHEMAS=scott,hr,oe \INCLUDE=TABLE:\"IN \(\'EMP\', \'EMPLOYEES\', \'CATEGORIES_TAB\'\)\"\3. For additional details, see also:Note 341733.1 - Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects Solution 2: Use multiple Data Pump jobs.Alternatively, you can also run multiple jobs, e.g.: -- Export table: scott.emp 12% expdp system/ DIRECTORY= DUMPFILE=.dmp \LOGFILE=expdp_tab1.log TABLES=scott.emp-- Export table: hr.employees 12% expdp system/ DIRECTORY= DUMPFILE=.dmp \LOGFILE=expdp_tab2.log TABLES=hr.employees-- Export table: oe.categories_tab 12% expdp system/ DIRECTORY= DUMPFILE=.dmp \LOGFILE=expdp_tab3.log TABLES=oe.categories_tabRemarks: \1. Oracle Data Pump restrictions for the TABLES parameter: - All table names specified must reside in the same schema. - To specify a schema other than your own, you must have the EXP_FULL_DATABASE role. - The use of wildcards is supported for one table name per export operation. \2. For additional details, see also:Note 277899.1 - Export/Import DataPump: UDE-00014 When Using Wildcard in the TABLES Parameter \3. Another example if you want to exclude two specific tables (SCOTT.EMP and HR.EMPLOYEES) from a full database export Data Pump job: -- Run a full database export, excluding all EMP and EMPLOYEES tables: 123% expdp system// DIRECTORY= DUMPFILE=expdp_f.dmp \ LOGFILE=expdp_f.log FULL=Y \EXCLUDE=TABLE:\"IN \(\'EMP\', \'EMPLOYEES\'\)\"-- Export tables EMP owned by users other than SCOTT: 12% expdp system// DIRECTORY= DUMPFILE=expdp_tab1.dmp \ LOGFILE=expdp_tab1.log SCHEMAS=oe,hr INCLUDE=TABLE:\"=\'EMP\'\"-- To export tables EMPLOYEES owned by users other than HR: 12% expdp system// DIRECTORY= DUMPFILE=expdp_tab2.dmp \ LOGFILE=expdp_tab2.log SCHEMAS=scott,oe INCLUDE=TABLE:\"=\'EMPLOYEES\'\"REFERENCESNOTE:341733.1 - Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific ObjectsNOTE:351598.1 - Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges) NOTE:277899.1 - Export/Import DataPump: UDE-00014 When Using Wildcard in the TABLES Parameter 总结数据泵要排除某个表，表前不能加用户名，尽管命令不报错，但是会导致EXCLUDE的TABLE选项排除失效，正确写法，如下 12345 expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp \EXCLUDE=STATISTICS,TABLE:"in('T3')" \LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y如下，不能排除LHR.T3表，虽然不报错，但是会导致EXCLUDE的TABLE选项排除失效： 1EXCLUDE=STATISTICS,TABLE:"in('LHR'.'T3')" SCHEMAS=LHR1,LHR如下，会排除T3表，但是会排除掉LHR和LHR1中的所有T3表， 1EXCLUDE=STATISTICS,TABLE:"in('T3')" SCHEMAS=LHR1,LHR解决办法：1、使用parfile和QUERY只导出元数据，而不导出某个具体表： 123456 vi parfile.parQUERY=LHR.T3:"where 1 1" expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp EXCLUDE=STATISTICS LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y parfile=parfile.par2、若LHR1和LHR用户下，只有LHR用户含T3表，则也可以使用如下的SQL 12345 expdp \'/ AS SYSDBA\' directory=D1 dumpfile=lhr_%U.dmp \EXCLUDE=STATISTICS,TABLE:"in('T3')" \LOGFILE=expdp_1.log SCHEMAS=LHR1,LHR CLUSTER=N COMPRESSION=ALL parallel=4 FILESIZE=2g REUSE_DUMPFILES=y对于某个表，可以单独进行导出和导入： 12expdp \'/ AS SYSDBA\' directory=D1 dumpfile=T3.dmp EXCLUDE=STATISTICS tables="LHR"."T3" CLUSTER=N COMPRESSION=ALL 其它数据泵常用写法参考：https://www.xmmup.com/oracleshujubengdaochudaoruchangyongxiefa.html 相关文章Oracle数据泵(expdp和impdp)导出导入常用写法Oracle如何提高或加速数据泵(expdp和impdp)的操作性能Oracle数据泵(expdp和impdp)导出导入报错stopped due to fatal errorOracle数据泵expdp和impdp之parallel参数在Oracle中，如何彻底停止expdp或impdp进程？Oracle数据泵的兼容性 Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions (Doc ID 553337.1)数据泵导入报错UDI-03113、UDI-03114和ORA-03113、ORA-03114、ORA-04036参数pga_aggregate_limit太小impdp数据泵导入使用table_exists_action=SKIP存在的问题以及如何接着导入数据而不用重新导入Oracle 11.2.0.4 数据泵expdp导出含CLOB字段(basicfile)的表超级慢的问题Oracle数据泵expdp导出报ORA-01555和ORA-22924Oracle 11.2.0.4数据泵expdp导出报DBMS_AW_EXP等信息Streams Pool过小导致expdp导出变慢数据泵impdp导入卡住，等待事件wait for unread message on broadcast channel在Oracle中，数据泵(expdp和impdp)导出导入之parfile的用法EXPDP导出导入表结构不分配存储空间案例Oracle 12C 数据泵新特性（DISABLE_ARCHIVE_LOGGING+VIEWS_AS_TABLES导出视图+LOGTIME）数据泵导入报错ORA-31626 job does not exist数据泵导入卡住，等待事件wait for unread message on broadcast channel数据泵导入碰到UDI-31623和ORA-31623 a job is not attached to this session via the specified handle错误Oracle的PDB数据库创建directory时遇到ORA-65254打赏赞(2)分享 |
| CopyRight 2018-2019 实验室设备网 版权所有 |